insert overwrite table jms_dm.dm_dev_inout_center_network_summary_dt
    partition
    (dt)
select scan_site_code                                                                     as scan_site_code    --扫描中心code
     , scan_site_name                                                                     as scan_site_name    --扫描中心name
     , site_agent_code                                                                    as site_agent_code   --代理区code
     , site_agent_name                                                                    as site_agent_name   --代理区name
     , out_way_num                                                                        as out_way_num       --出港量 出港件量（票） <中心出港件量>
     , out_pack_way_num                                                                   as out_pack_way_num  --出港到件包内票量（票） <中心出港包内件量>
     , (out_zddev_way_num + out_rgdev_way_num)                                            as out_build_way_num --出港 本中心建包的票数
     , out_zddev_way_num                                                                  as out_zddev_way_num --出港 自动线建包量（票）
     , out_rgdev_way_num                                                                  as out_rgdev_way_num --出港 人工建包量（票）
     , in_way_num                                                                         as in_way_num        --进港件量（票） <中心进港件量>
     , in_pack_way_num                                                                    as in_pack_way_num   --进港到件包内票量（票） <中心进港包内件量>
     , in_build_pack_num                                                                  as in_build_way_num  --进港 本中心建包的票数
     , in_tear_way_num                                                                    as in_tear_way_num   --进港 拆包包内票总数（票）
     , in_tear_pack_num                                                                   as in_tear_pack_num  --进港 拆包数
     , (out_zddev_way_num + out_rgdev_way_num)                                            as out_dev_num       --出港中心建包量（票）
     , sum(out_way_num) over ()                                                           as total_out_way_num --所有中心出港总件量
     , sum(in_way_num) over ()                                                            as total_in_way_num  --所有中心进港总件量
     , (out_pack_way_num + (out_zddev_way_num + out_rgdev_way_num))                       as out_sum           --（出港到件包内票量+中心建包量）
     , ((out_pack_way_num + (out_zddev_way_num + out_rgdev_way_num)) / out_way_num) * 100 as out_small_rate    --出港小件占比
     , (out_way_num / sum(out_way_num) over ()) * 100                                     as out_way_rate      --操作量占比(出港)
     , (in_way_num / sum(in_way_num) over ()) * 100                                       as in_way_rate       --操作量占比(进港)
     , (in_pack_way_num / in_way_num) * 100                                               as in_small_rate     --进港到件小件占比
     , (in_build_pack_num / in_way_num) * 100                                             as in_pack_rate      --进港建包率
     , date_add('{{ execution_date | cst_ds }}', -1)                                      as date_time         --日期
     , date_add('{{ execution_date | cst_ds }}', -1)                                      as dt
from (
         select network_code     as scan_site_code    --扫描中心code
              , network_name     as scan_site_name    --扫描中心name
              , agent_code       as site_agent_code   --代理区code
              , agent_name       as site_agent_name   --代理区name
              , case
                    when same_city_port_type = 2 and transfer_port_type = 2
                        then out_way_num + same_way_num + transfer_way_num
                    when same_city_port_type = 2 and transfer_port_type = 1 then out_way_num + same_way_num
                    when same_city_port_type = 1 and transfer_port_type = 2 then out_way_num + transfer_way_num
                    else out_way_num
             end                 as out_way_num       --出港量 出港件量（票） <中心出港件量>
              , case
                    when same_city_port_type = 2 and transfer_port_type = 2
                        then out_pack_way_num + same_pack_way_num + transfer_pack_way_num
                    when same_city_port_type = 2 and transfer_port_type = 1 then out_pack_way_num + same_pack_way_num
                    when same_city_port_type = 1 and transfer_port_type = 2
                        then out_pack_way_num + transfer_pack_way_num
                    else out_pack_way_num
             end                 as out_pack_way_num  --出港到件包内票量（票） <中心出港包内件量>
              , case
                    when same_city_port_type = 2
                        then out_zddev_way_num + same_zddev_way_num
                    else out_zddev_way_num
             end                 as out_zddev_way_num --出港 自动线建包量（票）
              , case
                    when same_city_port_type = 2
                        then out_rgdev_way_num + same_rgdev_way_num
                    else out_rgdev_way_num
             end                 as out_rgdev_way_num --出港 人工建包量（票）
              , case
                    when same_city_port_type = 1 and transfer_port_type = 1
                        then in_way_num + same_way_num + transfer_way_num
                    when same_city_port_type = 1 and transfer_port_type = 2 then in_way_num + same_way_num
                    when same_city_port_type = 2 and transfer_port_type = 1 then in_way_num + transfer_way_num
                    else in_way_num
             end                 as in_way_num        --进港件量（票） <中心进港件量>
              , case
                    when same_city_port_type = 1 and transfer_port_type = 1
                        then in_pack_way_num + same_pack_way_num + transfer_pack_way_num
                    when same_city_port_type = 1 and transfer_port_type = 2 then in_pack_way_num + same_pack_way_num
                    when same_city_port_type = 2 and transfer_port_type = 1 then in_pack_way_num + transfer_pack_way_num
                    else in_pack_way_num
             end                 as in_pack_way_num   --进港到件包内票量（票） <中心进港包内件量>
              , in_tear_way_num  as in_tear_way_num   --进港 拆包包内票总数（票）
              , in_tear_pack_num as in_tear_pack_num  --进港 拆包数
              , case
                    when same_city_port_type = 1
                        then in_build_pack_num + same_zddev_way_num + same_rgdev_way_num
                    else in_build_pack_num
             end                 as in_build_pack_num
         from jms_dm.dm_dev_center_operate_summary_dt
         where dt = date_add('{{ execution_date | cst_ds }}', -1)
     ) a
    distribute by dt
;

